package com.njlime.core.utils;

import com.alibaba.fastjson.JSONArray;
import com.njlime.core.helper.dic.DictionaryHelper;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Method;
import java.util.*;

/**
 * <b>处理excel文件的工具类</b>
 *
 * @author GQ
 * @date 2016年12月27日 上午10:39:56
 * @status
 */
public class ExcelUtils {

    private static int MAX_SHOW_ROW_PER_SHEET = 50000;

    /**
     * 方法描述：用于下载文件,根据存在的文件绝对路径下载.<br>
     * 返回类型：void<br>
     * 作者:GQ<br>
     * 创建时间：2016年12月27日上午10:51:17
     *
     * @param filePath 欲下载的文件绝对路径.<br>如:"F:\apache-tomcat-7.0.70\webapps\Rs_sys\WEB-INF\download\xxx.xls"
     * @param request  request请求
     * @param response response请求<br>
     */
    public static void download(String filePath, HttpServletRequest request, HttpServletResponse response) {
        HSSFWorkbook workbook = null;
        ServletOutputStream sos = null;
        try {
            File file = new File(filePath);
            if (!file.exists()) {
                response.sendError(404, "File not found!");
                return;
            }
            BufferedInputStream br = new BufferedInputStream(new FileInputStream(
                    filePath));
            String downLoadName = null;
            String agent = request.getHeader("USER-AGENT");
            if (null != agent && -1 != agent.indexOf("MSIE")) { // IE
                downLoadName = java.net.URLEncoder.encode(file.getName(), "UTF-8");
            } else if (null != agent && -1 != agent.indexOf("Mozilla")) { // Firefox
                downLoadName = new String(file.getName().getBytes("UTF-8"), "iso-8859-1");
            } else {
                downLoadName = java.net.URLEncoder.encode(file.getName(), "UTF-8");
            }
            byte[] buf = new byte[1024];
            int len = 0;
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename="
                    + downLoadName);
            OutputStream out = response.getOutputStream();
            while ((len = br.read(buf)) > 0) {
                out.write(buf, 0, len);
            }
            br.close();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != sos) {
                    sos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 方法描述：获取单元格中的数据,根据存储的值的类型不同,调用相应的方法获取数据,最后返回一个字符串类型的数据<br>
     * 返回类型：String<br>
     * 作者:GQ<br>
     * 创建时间：2016年12月28日下午6:24:00
     *
     * @param cell 单元格
     * @return<br>
     */
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        //DecimalFormat df = new DecimalFormat("#");
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                cellValue = cell.getRichStringCellValue().getString().trim();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                cellValue = String.valueOf(new Integer((int) cell.getNumericCellValue()));
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                cellValue = cell.getCellFormula();
                break;
            default:
                cellValue = "";
        }
        return cellValue;
    }

    /**
     * 方法描述：文件写入<br>
     * 返回类型：void<br>
     * 作者:GQ<br>
     * 创建时间：2016年12月30日下午1:41:24
     *
     * @param file         为获得到的文件(也就是要讲这个文件写到哪里)
     * @param filePath     文件写入的位置
     * @param fileName<br> 文件的名
     */
    public static void write(MultipartFile file, String filePath, String fileName) {
        //在指定目录下建立一个空的文件,作用:别的file文件往里写入
        File targetFile = new File(filePath, fileName);
        if (!targetFile.exists()) {
            targetFile.mkdirs();
        }
        //保存
        try {
            //将前台传过来的file文件写到targetFile中.
            file.transferTo(targetFile);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 方法描述：创建行,rowNum控制创建第几行.sheet控制在哪个工作簿上创建<br>
     * 返回类型：HSSFRow<br>
     * 作者:GQ<br>
     * 创建时间：2017年1月3日下午4:58:09
     *
     * @param sheet  工作簿
     * @param rowNum 行数(下标从0开始)
     * @return<br>
     */
    public static HSSFRow createRow(HSSFSheet sheet, int rowNum) {
        return sheet.getRow(rowNum) != null ? sheet.getRow(rowNum) : sheet.createRow(rowNum);
    }

    /**
     * 方法描述：创建一个cell,样式,值.<br>
     * 返回类型：void<br>
     * 作者:GQ<br>
     * 创建时间：2017年1月3日下午4:02:32<br>
     *
     * @param cellNum 第几个cell,下标从0开始,即0代表第一个<br>
     * @param value   该cell的值<br>
     * @param rowNum  行<br>
     * @param style   样式(采用哪个样式)<br>
     */
    public static HSSFCell createCell(HSSFSheet sheet, int rowNum, int cellNum, HSSFCellStyle style, String value) {
        HSSFRow row = createRow(sheet, rowNum);
        HSSFCell cell = row.createCell((short) cellNum);
        cell.setCellValue(new HSSFRichTextString(value));
        cell.setCellStyle(style);
        return cell;
    }

    /**
     * 方法描述：创建多个单元格.必须是同一行的.并赋值上相应数据<br>
     * 返回类型：HSSFCell<br>
     * 作者:GQ<br>
     * 创建时间：2017年1月3日下午5:21:52
     *
     * @param sheet    工作簿
     * @param rowNumm  行数
     * @param cellNums 要创建的单元格下标数组
     * @param style    样式
     * @param values   单元格下标对应的值,一个对应一个,顺序不能乱
     */
    public static List<HSSFCell> createMultiCell(HSSFSheet sheet, int rowNumm, Integer[] cellNums, HSSFCellStyle style, String[] values) {
        List<HSSFCell> cellList = new ArrayList<HSSFCell>();
        for (int i = 0; i < cellNums.length; i++) {
            HSSFCell cell = createCell(sheet, rowNumm, cellNums[i], style, values[i]);
            cellList.add(cell);
        }
        return cellList;
    }

    /**
     * 方法描述：创建自定义cell单元格的基础样式.<br>
     * 返回类型：HSSFCellStyle<br>
     * 作者:GQ<br>
     * 创建时间：2017年1月3日下午4:04:02
     *
     * @param workbook
     */
    public static HSSFCellStyle getCellStyle(HSSFWorkbook workbook) {
        //设置字体;
        //HSSFFont font = workbook.createFont();
        //设置字体大小;
        //font.setFontHeightInPoints((short) 5);
        //设置字体名字;
        //font.setFontName("Courier New");
        //font.setItalic(true);
        //font.setStrikeout(true);
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        //style.setFont(font);
        //设置自动换行;
        style.setWrapText(true);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    /**
     * 方法描述：自定义设置标题样式,只有下边框<br>
     * 返回类型：HSSFCellStyle<br>
     * 作者:GQ<br>
     * 创建时间：2017年1月3日下午4:20:42<br>
     *
     * @param workbook<br>
     */
    public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 15);
        style.setFont(font);
        return style;
    }

    /**
     * 方法描述：合并单元格<br>
     * 返回类型：int<br>
     * 作者:GQ<br>
     * 创建时间：2017年1月4日上午10:45:20<br>
     *
     * @param sheet    工作簿<br>
     * @param firstRow 合并起止行(下标从0开始)<br>
     * @param lastRow  合并截止行(下标从0开始)<br>
     * @param firstCol 合并起止列(下标从0开始)<br>
     * @param lastCol  合并截止列(下标从0开始)<br>
     */
    public static int mergedRegion(HSSFSheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    /**
     * 方法描述：将生成好的excel文件,写入到指定路径下的文件中.<br>
     * 返回类型：void<br>
     * 作者:GQ<br>
     * 创建时间：2017年1月4日上午10:57:04<br>
     *
     * @param wb           一个workbook，对应一个Excel文件<br>
     * @param absolutePath excel文件的绝对路径<br>
     */
    public static void writeExcelToLocation(HSSFWorkbook wb, String absolutePath) {
        try {
            FileOutputStream fout = new FileOutputStream(absolutePath);
            wb.write(fout);
            fout.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 方法描述：获取workbook,将文件转换为workbook对象，读取excel时可用<br>
     * 返回类型：Workbook<br>
     * 作者:GQ<br>
     * 创建时间：2017年1月4日下午5:12:20<br>
     *
     * @param absolutePath 文件的绝对路径(excel文件)<br>
     * @return<br>
     */
    public static Workbook getWorkBook(String absolutePath) {
        FileInputStream fis = null;
        Workbook wb = null;
        try {
            // 获取一个绝对地址的流
            fis = new FileInputStream(absolutePath);
            wb = new HSSFWorkbook(fis);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return wb;
    }

    /**
     * 导出excel，单个sheet，数据过多时会自动扩展为多sheet
     *
     * @param titleColumn 对应bean的属性名
     * @param titleName   excel要导出的列名
     * @param dataList    数据
     * @param tableName 导出表标题
     */
    @SuppressWarnings("unchecked")
    public static void exportExcel(HttpServletResponse response, String titleColumn[], String titleName[]
            , String dataDic[], String extra[], List<?> dataList, String tableName) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        if(dataList == null){
            dataList = new ArrayList<>();
        }
        int sheetNum = dataList.size() / MAX_SHOW_ROW_PER_SHEET + 1;
        OutputStream out = null;
        try {
            for (int sheetIndex = 1; sheetIndex <= sheetNum; sheetIndex++) {
                String sheetName = "sheet" + sheetIndex;
                Sheet sheet = workbook.createSheet(sheetName);
                int row = 0;
                //写入标题
                if (!StringUtil.isEmpty(tableName)) {
                    row++;
                }
                //写入表头
                Row headRow = workbook.getSheet(sheetName).createRow(row);
                headRow.setHeight((short) 350);
                HSSFCellStyle dataStyle = getCellStyle(workbook);
                int titleIndex = 0;
                JSONArray colSize = new JSONArray();
                for (int i = 0; i < titleName.length; i++) {
                    if(extra != null && extra.length > i && "jsonArray".equals(extra[i])) {
                        JSONArray titleArray = JSONArray.parseArray(titleName[i]);
                        for(int j = 0; j < titleArray.size(); j++){
                            Cell cell = headRow.createCell(titleIndex);
                            cell.setCellStyle(dataStyle);
                            cell.setCellValue(titleArray.getString(j));
                            colSize.set(titleIndex, titleArray.getString(j).getBytes("utf-8").length);
                            titleIndex++;
                        }
                    }else{
                        Cell cell = headRow.createCell(titleIndex);
                        cell.setCellStyle(dataStyle);
                        cell.setCellValue(titleName[i]);
                        colSize.set(titleIndex, titleName[i].getBytes("utf-8").length);
                        titleIndex++;
                    }
                }
                if(!StringUtil.isEmpty(tableName)){
                    Row titleRow = workbook.getSheet(sheetName).createRow(0);
                    titleRow.setHeight((short) 500);
                    HSSFCellStyle titleStyle = getTitleStyle(workbook);
                    mergedRegion((HSSFSheet) sheet, 0, 0, 0, titleIndex - 1);
                    Cell cell = titleRow.createCell(0);
                    cell.setCellStyle(titleStyle);
                    cell.setCellValue(tableName);
                }
                row++;

                //通过反射获取数据并写入到excel中
                if (dataList != null && dataList.size() > 0) {
                    //设置样式
                    if (titleColumn.length > 0) {
                        int startRow = (sheetIndex - 1) * MAX_SHOW_ROW_PER_SHEET;
                        int endRow = dataList.size() > (sheetIndex*MAX_SHOW_ROW_PER_SHEET) ? (sheetIndex*MAX_SHOW_ROW_PER_SHEET) : dataList.size();
                        for (int rowIndex = startRow; rowIndex < endRow; rowIndex++) {
                            Object obj = dataList.get(rowIndex);     //获得该对象
                            Class clazz = obj.getClass();     //获得该对对象的class实例
                            Row dataRow = workbook.getSheet(sheetName).createRow(row);
                            dataRow.setHeight((short) 350);
                            int dataIndex = 0;
                            for (int columnIndex = 0; columnIndex < titleColumn.length; columnIndex++) {
                                String title = titleColumn[columnIndex].trim();
                                if (!"".equals(title)) {  //字段不为空
                                    String data = "";
                                    String returnType = "";
                                    if(clazz == HashMap.class){
                                        data = ((Map<String, Object>)obj).get(title) == null ? "" : ((Map<String, Object>)obj).get(title).toString();
                                        returnType = ((Map<String, Object>)obj).get(title) == null ? "String" : ((Map<String, Object>)obj).get(title).getClass().getSimpleName();
                                    }else{
                                        //使首字母大写
                                        String UTitle = Character.toUpperCase(title.charAt(0)) + title.substring(1, title.length()); // 使其首字母大写;
                                        String methodName = "get" + UTitle;
                                        // 设置要执行的方法
                                        Method method = clazz.getDeclaredMethod(methodName);
                                        //获取返回类型
                                        returnType = method.getReturnType().getSimpleName();
                                        data = method.invoke(obj) == null ? "" : method.invoke(obj).toString();
                                    }
                                    if(extra != null && extra.length > columnIndex && !StringUtil.isEmpty(extra[columnIndex])) {
                                        String deal = dealExtra(columnIndex, extra, data);
                                        if (!StringUtil.isEmpty(deal)) {
                                            returnType = "String";
                                            data = deal;
                                        }
                                    }
                                    //excel数据插入
                                    if (data != null) {
                                        if(extra != null && extra.length > columnIndex && "jsonArray".equals(extra[columnIndex])) {
                                            JSONArray dataArray = JSONArray.parseArray(data);
                                            for(int i = 0; i < dataArray.size(); i++){
                                                Cell cell = dataRow.createCell(dataIndex);
                                                cell.setCellStyle(dataStyle);
                                                colSize.set(dataIndex, colSize.getInteger(dataIndex) > dataArray.getString(i).getBytes("utf-8").length ? colSize.getInteger(dataIndex) : dataArray.getString(i).getBytes("utf-8").length);
                                                cell.setCellValue(dataArray.getString(i));
                                                if (rowIndex == (endRow - 1)) {
                                                    sheet.setColumnWidth(dataIndex , (colSize.getInteger(dataIndex) + 5) * 256);
                                                }
                                                dataIndex++;
                                            }
                                        }else{
                                            Cell cell = dataRow.createCell(dataIndex);
                                            cell.setCellStyle(dataStyle);
                                            colSize.set(dataIndex, colSize.getInteger(dataIndex) > data.getBytes("utf-8").length ? colSize.getInteger(dataIndex) : data.getBytes("utf-8").length);
                                            if ("Integer".equals(returnType)) {
                                                if (dataDic != null && dataDic.length > columnIndex && !StringUtil.isEmpty(dataDic[columnIndex])) {
                                                    cell.setCellValue(DictionaryHelper.getInstance().getDicDisplay(dataDic[columnIndex], Integer.parseInt(data)));
                                                } else {
                                                    cell.setCellValue(Integer.parseInt(data));
                                                }
                                            } else if ("Long".equals(returnType)) {
                                                if (dataDic != null && dataDic.length > columnIndex && !StringUtil.isEmpty(dataDic[columnIndex])) {
                                                    cell.setCellValue(DictionaryHelper.getInstance().getDicDisplay(dataDic[columnIndex], Long.parseLong(data)));
                                                } else {
                                                    cell.setCellValue(Long.parseLong(data));
                                                }
                                            } else if ("Double".equals(returnType)) {
                                                if (dataDic != null && dataDic.length > columnIndex && !StringUtil.isEmpty(dataDic[columnIndex])) {
                                                    cell.setCellValue(DictionaryHelper.getInstance().getDicDisplay(dataDic[columnIndex], StringUtil.formatDouble(Double.parseDouble(data), 2)));
                                                } else {
                                                    cell.setCellValue(StringUtil.formatDouble(Double.parseDouble(data), 2));
                                                }
                                            } else {
                                                if (dataDic != null && dataDic.length > columnIndex && !StringUtil.isEmpty(dataDic[columnIndex])) {
                                                    cell.setCellValue(DictionaryHelper.getInstance().getDicDisplay(dataDic[columnIndex], data));
                                                } else {
                                                    cell.setCellValue(data);
                                                }
                                            }
                                            if (rowIndex == (endRow - 1)) {
                                                sheet.setColumnWidth(dataIndex , (colSize.getInteger(dataIndex) + 5) * 256);
                                            }
                                            dataIndex++;
                                        }
                                    }
                                }
                            }
                            row++;
                        }
                    }
                }
            }
            //直接写到输出流中
            out = response.getOutputStream();
            String fileName = new String((tableName + DateUtil.dateFormat(new Date(), "yyyyMMdd") + ".xls").getBytes(), "ISO-8859-1");
            response.reset();
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 数据进行额外处理
     * @param index
     * @param extra
     * @return
     */
    private static String dealExtra(int index, String[] extra,String data){
        if(extra[index].contains("amount")){
            return StringUtil.formatDouble((Double.valueOf(data)/100),2) + "";
        }else if(extra[index].contains("time")){
            return DateUtil.dateFormat(DateUtil.stringToDate(data),"yyyy-MM-dd HH:mm:ss");
        }
        return data;
    }

}
